import pymysql
from config.config import DB_CONFIG
import traceback
from datetime import datetime
import uuid

def get_db_connection():
    """获取数据库连接"""
    try:
        print("\n=== Database Connection Info ===")
        print(f"Host: {DB_CONFIG['host']}")
        print(f"User: {DB_CONFIG['user']}")
        print(f"Database: {DB_CONFIG['database']}")
        
        conn = pymysql.connect(
            host=DB_CONFIG['host'],
            user=DB_CONFIG['user'],
            password=DB_CONFIG['password'],
            database=DB_CONFIG['database'],
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
        print("Database connection successful")
        print("===========================\n")
        return conn
        
    except pymysql.Error as err:
        print("\n=== Database Connection Error ===")
        print(f"Error: {str(err)}")
        if hasattr(err, 'args') and len(err.args) > 0:
            if err.args[0] == 1045:
                print("Error: Access denied (wrong username or password)")
            elif err.args[0] == 1049:
                print("Error: Database does not exist")
            elif err.args[0] == 2003:
                print("Error: Cannot connect to MySQL server")
        print("===========================\n")
        return None

def check_db_connection():
    """检查数据库连接是否可用"""
    conn = get_db_connection()
    if conn is None:
        return False
    conn.close()
    return True

def init_db():
    """初始化数据库表"""
    try:
        conn = get_db_connection()
        if conn is None:
            print("数据库连接失败，无法初始化数据库")
            return False
            
        cursor = conn.cursor()
        
        # 读取SQL文件
        try:
            with open('database/init.sql', 'r', encoding='utf-8') as f:
                sql_commands = f.read().split(';')
        except FileNotFoundError:
            print("找不到初始化SQL文件: database/init.sql")
            return False
        except Exception as e:
            print(f"读取SQL文件时出错: {str(e)}")
            return False
            
        # 执行每个SQL命令
        for i, command in enumerate(sql_commands):
            if command.strip():
                try:
                    cursor.execute(command)
                    print(f"执行SQL命令 {i+1}/{len(sql_commands)}")
                except Exception as e:
                    print(f"执行SQL命令时出错: {str(e)}")
                    print(f"问题SQL: {command[:100]}...")
                    conn.rollback()
                    return False
        
        # 添加默认管理员账户
        try:
            admin_id = str(uuid.uuid4())
            admin_password = "admin123"  # 实际应用中应该加密
            cursor.execute(
                "INSERT INTO user (user_id, username, password, email, role, created_at) "
                "VALUES (%s, %s, %s, %s, %s, %s)",
                (admin_id, "admin", admin_password, "admin@example.com", "admin", datetime.now())
            )
        except Exception as e:
            print(f"添加管理员账户时出错: {str(e)}")
            conn.rollback()
            return False
        
        conn.commit()
        cursor.close()
        conn.close()
        return True
    except Exception as e:
        print(f"数据库初始化过程中出错: {str(e)}")
        print(traceback.format_exc())
        return False